package com.resataurant.Dao;

import com.resataurant.Entity.Admin;
import com.resataurant.Entity.Dish;
import com.resataurant.Entity.User;
import com.resataurant.Util.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class AdminDao {

    public Admin Login(String username, String password) {
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Admin admin = new Admin();
        try {
            String sql = "select * from s_user where s_id = '" + username + "'";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                admin.setId(resultSet.getString(1));
                admin.setUsername(resultSet.getString(4));
                admin.setPassword(resultSet.getString(2));
                admin.setPow(resultSet.getInt(3));
                System.out.println("数据库检索到的用户名：" + resultSet.getString(4));
                return admin;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(connection, preparedStatement, resultSet);
        }
        return null;
    }

    public int addDish(Dish dish) {
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            String sql = "insert into s_shop (w_id, w_name, w_contenct, w_price, w_user_id,w_path) values (?, ?, ?, ?, ?,?)";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, dish.getDishId());
            preparedStatement.setString(2, dish.getDishName());
            preparedStatement.setString(3, dish.getDishContent());
            preparedStatement.setString(4, dish.getDishPrice());
            preparedStatement.setString(5, dish.getUserId());
            preparedStatement.setString(6, dish.getDishImage());
            return preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(connection, preparedStatement, resultSet);
        }
        return 0;
    }

    public List<Dish> searchDish(String dishName) {
        String sql = "select * from s_shop where w_name like '%" + dishName + "%'";
        List<Dish> list = new ArrayList<>();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = JDBCUtils.getConnection().prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Dish dish = new Dish();
                dish.setDishId(resultSet.getString(1));
                dish.setDishName(resultSet.getString(2));
                dish.setDishContent(resultSet.getString(3));
                dish.setDishPrice(resultSet.getString(4));
                dish.setDishImage(resultSet.getString(6));
                dish.setUserId(resultSet.getString(5));
                list.add(dish);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(JDBCUtils.getConnection(), preparedStatement, resultSet);
        }
        return list;
    }

    public List<Dish> searchDish() {
        String sql = "select * from s_shop";
        List<Dish> list = new ArrayList<>();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = JDBCUtils.getConnection().prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Dish dish = new Dish();
                dish.setDishId(resultSet.getString(1));
                dish.setDishName(resultSet.getString(2));
                dish.setDishContent(resultSet.getString(3));
                dish.setDishPrice(resultSet.getString(4));
                dish.setDishImage(resultSet.getString(6));
                dish.setUserId(resultSet.getString(5));
                list.add(dish);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(JDBCUtils.getConnection(), preparedStatement, resultSet);
        }
        return list;
    }

    public Dish searchDishById(String id) {
        String sql = "select * from s_shop where w_id = ?";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection = null;
        try {
            connection = JDBCUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, id);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                Dish dish = new Dish();
                dish.setDishId(resultSet.getString(1));
                dish.setDishName(resultSet.getString(2));
                dish.setDishContent(resultSet.getString(3));
                dish.setDishPrice(resultSet.getString(4));
                dish.setDishImage(resultSet.getString(6));
                dish.setUserId(resultSet.getString(5));
                return dish;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(connection, preparedStatement, resultSet);
        }
        return null;
    }

    public int updateDish(Dish dish, String oldid) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtils.getConnection();
            String sql = "update s_shop set w_id = ?, w_name = ?, w_contenct = ?, w_price = ?, w_user_id = ?, w_path = ? where w_id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, dish.getDishId());
            preparedStatement.setString(2, dish.getDishName());
            preparedStatement.setString(3, dish.getDishContent());
            preparedStatement.setString(4, dish.getDishPrice());
            preparedStatement.setString(5, dish.getUserId());
            preparedStatement.setString(6, dish.getDishImage());
            preparedStatement.setString(7, oldid);
            return preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(connection, preparedStatement, null);
        }
        return 0;
    }

    public int deleteDish(String id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtils.getConnection();
            String sql = "delete from s_shop where w_id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, id);
            preparedStatement.executeUpdate();
            return 1;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(connection, preparedStatement, null);
        }
        return 0;
    }

    public List<User> searchUser() {
        String sql = "select * from s_user where s_pow = 1";
        List<User> list = new ArrayList<>();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = JDBCUtils.getConnection().prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                User user = new User();
                user.setId(resultSet.getString(1));
                user.setPassword(resultSet.getString(2));
                user.setPow(resultSet.getInt(3));
                user.setUsername(resultSet.getString(4));
                list.add(user);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            JDBCUtils.close(JDBCUtils.getConnection(), preparedStatement, resultSet);
        }
    }

    public List<User> searchUserBy(String username) {
        String sql = "select * from s_user where s_pow = 1 and s_name like '%" + username + "%'";
        List<User> list = new ArrayList<>();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = JDBCUtils.getConnection().prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                User user = new User();
                user.setId(resultSet.getString(1));
                user.setPassword(resultSet.getString(2));
                user.setPow(resultSet.getInt(3));
                user.setUsername(resultSet.getString(4));
                list.add(user);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            JDBCUtils.close(JDBCUtils.getConnection(), preparedStatement, resultSet);
        }
    }

    private int getUserTimes(String userId) {
        String sql = "select count(*) from s_shop_log where w_user_id = ?";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = JDBCUtils.getConnection().prepareStatement(sql);
            preparedStatement.setString(1, userId);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                return resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(JDBCUtils.getConnection(), preparedStatement, resultSet);
        }
        return 0;
    }

    private String getUserLast(String userId) {
        String sql = "select w_data from s_shop_log where w_user_id = ? order by w_data desc limit 1";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = JDBCUtils.getConnection().prepareStatement(sql);
            preparedStatement.setString(1, userId);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                return resultSet.getString(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(JDBCUtils.getConnection(), preparedStatement, resultSet);
        }
        return "无记录";
    }

    public User userDetail(User user) {
        user.setTimes(getUserTimes(user.getId()));
        user.setLastTime(getUserLast(user.getId()));
        return user;
    }

    public int register(User user) {
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            String sql = "insert into s_user (s_id, s_pwd, s_pow, s_name) values (?, ?, ?, ?)";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, user.getId());
            preparedStatement.setString(2, user.getPassword());
            preparedStatement.setInt(3, 1);
            preparedStatement.setString(4, user.getUsername());
            return preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }
}

